**
** GIS output organized by objectid 
** 26-Jul-2020
**

clear
import delimited "proc_data\ntl_dist_cities_2020-07-26"
rename *, lower

sort objectid

* need gid_0 to generate distance vars
drop id gid_1 name_1 name_2 gid_2 gid_3 name_3 name_0 inside_x inside_y

sum objectid

rename sumghs_built_lds1975_globe_r2016 built1975
rename sumghs_built_lds1990_globe_r2016 built1990
rename sumghs_built_lds2000_globe_r2016 built2000
rename sumghs_built_lds2014_globe_r2016 built2014

g dist2cap = .
replace dist2cap = dist2cap_cmr if gid_0=="CMR"
replace dist2cap = dist2cap_ner if gid_0=="NER"
replace dist2cap = dist2cap_nga if gid_0=="NGA"
replace dist2cap = dist2cap_tcd if gid_0=="TCD"

g dist2pri = .
replace dist2pri = dist2pri_cmr if gid_0=="CMR"
replace dist2pri = dist2pri_ner if gid_0=="NER"
replace dist2pri = dist2pri_nga if gid_0=="NGA"
replace dist2pri = dist2pri_tcd if gid_0=="TCD"

g dist2oil = .
replace dist2oil = dist2oil_cmr if gid_0=="CMR"
replace dist2oil = dist2oil_ner if gid_0=="NER"
replace dist2oil = dist2oil_nga if gid_0=="NGA"
replace dist2oil = dist2oil_tcd if gid_0=="TCD"

** update dist error for NGA
sort objectid
merge 1:1 objectid using "DIST.dta"
drop _merge

** update
replace dist2pri_nga = dist2pri2nga
replace dist2cap_nga = dist2cap2nga
replace dist2pri = dist2pri2nga if gid_0=="NGA"
replace dist2cap = dist2cap2nga if gid_0=="NGA"
sum dist2pri_nga dist2cap_nga
drop dist2pri2nga  dist2cap2nga

replace dist2bordbhbr = dist2bordbhbrnew
drop dist2bordbhbrnew
sum

** panel
reshape long meanntl_f10 meanntl_f12 meanntl_f14 meanntl_f15 meanntl_f16 meanntl_f18 meanparetof10 meanparetof12 meanparetof14 meanparetof15 meanparetof16 meanparetof18 meanntl_rad_f12 meanntl_rad_f14 meanntl_rad_f16 sumntl_f10 sumntl_f12 sumntl_f14 sumntl_f15 sumntl_f16 sumntl_f18 sumparetof10 sumparetof12 sumparetof14 sumparetof15 sumparetof16 sumparetof18 sumntl_rad_f12 sumntl_rad_f14 sumntl_rad_f16 pop built meanmod17a3_science_npp_ cmbcnf_ event_ fatal_, i(objectid) j(year) string

rename cmbcnf_ cmbcnf
rename event_ event
rename fatal_ fatal

replace cmbcnf="" if cmbcnf=="NA"
replace event="" if event=="NA"
replace fatal="" if fatal=="NA"

replace pop="" if pop=="NA"

sum cmbcnf event fatal

destring cmbcnf, replace
destring event, replace
destring fatal, replace
destring pop, replace

destring year, replace

rename meanmod17a3_science_npp npp
replace npp="" if npp=="NA"
destring npp, replace

rename meanntl_f18 ntl_f18
rename meanntl_f16 ntl_f16
rename meanntl_f15 ntl_f15
rename meanntl_f14 ntl_f14
rename meanntl_f12 ntl_f12
rename meanntl_f10 ntl_f10

rename meanparetof10 ntlpar_f10
rename meanparetof12 ntlpar_f12
rename meanparetof14 ntlpar_f14
rename meanparetof15 ntlpar_f15
rename meanparetof16 ntlpar_f16
rename meanparetof18 ntlpar_f18

rename meanntl_rad_f16 ntl_rad_f16
rename meanntl_rad_f14 ntl_rad_f14
rename meanntl_rad_f12 ntl_rad_f12


g ntl = ntl_f18 if year >= 2010
replace ntl = ntl_f16 if year < 2010 & year >= 2004
replace ntl = ntl_f14 if year < 2004 & year >= 1998
replace ntl = ntl_f12 if year < 1998 & year >= 1995
replace ntl = ntl_f10 if year < 1995 & year >= 1992

g ntlpar = ntlpar_f18 if year  >= 2010
replace ntlpar = ntlpar_f16 if year < 2010 & year >= 2004
replace ntlpar = ntlpar_f14 if year < 2004 & year >= 1998
replace ntlpar = ntlpar_f12 if year < 1998 & year >= 1995
replace ntlpar = ntlpar_f10 if year < 1995 & year >= 1992

g ntlsat = "f18" if year>=2010
replace ntlsat = "f16" if year<2010 & year>=2004
replace ntlsat = "f14" if year < 2004 & year >= 1998
replace ntlsat = "f12" if year < 1998 & year >= 1995
replace ntlsat = "f10" if year < 1995 & year >= 1992

g ntlradsat = "f12" if year==1996
replace ntlradsat = "f12f15" if year==2000
replace ntlradsat = "f14f15" if year==2003
replace ntlradsat = "f14" if year==2004
replace ntlradsat = "f16" if year==2005
replace ntlradsat = "f16" if year==2010

label variable ntl "Mean of night time lights DMSP-OLS : NOAA"
label variable ntl_f10 "Mean of night time lights F10 DMSP-OLS : NOAA"
label variable ntl_f12 "Mean of night time lights F12 DMSP-OLS : NOAA"
label variable ntl_f14 "Mean of night time lights F14 DMSP-OLS : NOAA"
label variable ntl_f15 "Mean of night time lights F15 DMSP-OLS : NOAA"
label variable ntl_f16 "Mean of night time lights F16 DMSP-OLS : NOAA"
label variable ntl_f18 "Mean of night time lights F18 DMSP-OLS : NOAA"
label variable ntlsat "DMSP-OLS satellite of panel ntl & ntlpar"
label variable ntlrad "Radiance calibrated night time lights adj. T-S DMSP-OLS : NOAA"
label variable ntlradsat "DMSP-OLS satellite of panel ntlrad"
label variable ntlpar "Pareto corrected night time lights : Bluhm Krause 2019"

label variable ntlpar_f10 "Pareto corrected night time lights F10 : Bluhm Krause 2019"
label variable ntlpar_f12 "Pareto corrected night time lights F12 : Bluhm Krause 2019"
label variable ntlpar_f14 "Pareto corrected night time lights F14 : Bluhm Krause 2019"
label variable ntlpar_f15 "Pareto corrected night time lights F15 : Bluhm Krause 2019"
label variable ntlpar_f16 "Pareto corrected night time lights F16 : Bluhm Krause 2019"
label variable ntlpar_f18 "Pareto corrected night time lights F18 : Bluhm Krause 2019"

label variable ntl_rad_f12 "Mean rad calibrated adj. F12 T-S DMSP-OLS : NOAA"
label variable ntl_rad_f14 "Mean rad calibrated adj. F14 T-S DMSP-OLS : NOAA"
label variable ntl_rad_f16 "Mean rad calibrated adj. F16 T-S DMSP-OLS : NOAA"

label variable npp "Mean of Net primary productivity (Kg C / m^2) : MODIS"
label variable built "Mean of built-up from 250m pixels : GHS"

label variable cmbcnf "Number of combined BH conflict : calc + ACLED"
label variable event "Number of BH events : ACLED"
label variable fatal "Number of BH fatalities : ACLED"


rename sumr_area_km2 r_area_km2
label variable r_area_km2 "Area (km2) : GIS calc in R"
label variable sf_area_km2 "Area (km2) : GIS calc in sf"
label variable negdist2bhc "Negative dist to Maiduguri (km2) : calc - Africapolis"
label variable dist2bhc "Dist to Maiduguri (km2) : calc - Africapolis"
label variable dist2bordbh3s "Distance to border BH 3 states"
label variable dist2bordbhb "Distance to border BH Borno"
label variable dist2bordbhbr "Distance to border BH between rivers"

label variable dist2cap "Dist to capital in 4 countries : calc Africapolis"
label variable dist2cap_cmr "Dist to capital in cmr : calc Africapolis"
label variable dist2cap_ner "Dist to capital in ner : calc Africapolis"
label variable dist2cap_nga "Dist to capital in nga : calc Africapolis"
label variable dist2cap_tcd "Dist to capital in tcd : calc Africapolis"

label variable dist2pri "Dist to primary city in 4 countries (2000) : calc Africapolis"
label variable dist2pri_cmr "Dist to primary city in cmr (2000) : calc Africapolis"
label variable dist2pri_ner "Dist to primary city in ner (2000) : calc Africapolis"
label variable dist2pri_nga "Dist to primary city in nga (2000) : calc Africapolis"
label variable dist2pri_tcd "Dist to primary city in tcd (2000) : calc Africapolis"

label variable dist2oil "Dist to Oil Fields in 4 countries (km2) : calc Petro Dataset v.1.1" 
label variable dist2oil_cmr "Dist to Oil Fields in cmr (km2) : calc Petro Dataset v.1.1" 
label variable dist2oil_ner "Dist to Oil Fields in ner (km2) : calc Petro Dataset v.1.1" 
label variable dist2oil_nga "Dist to Oil Fields in nga (km2) : calc Petro Dataset v.1.1" 
label variable dist2oil_tcd "Dist to Oil Fields in tcd (km2) : calc Petro Dataset v.1.1" 

label variable pop "City population (total) : Africapolis"

label variable year "Year"

* panel grid cell year
sort objectid year
xtset objectid year

tab year ntlsat

sum ntl ntlpar, d


drop meanr_area_km2 

sort objectid year
save "proc_data\NTL_POP_ACLED_ts.dta", replace

**
** data checks
**
sum objectid

tab year
